import time
import os
import records
import cx_Oracle
import pathlib
from sqlalchemy.exc import ResourceClosedError, OperationalError

from ..lib.bwd_ssh_db import bwd_ssh_for_db
from ..globals import g
from ..log import logger
from ..utility import get_keyword_param, compare
from ..config import result_path


def query_bwd_ssh(step: dict):
    """通过堡旺达堡垒机ssh服务连接数据库，通过sqlplus查询数据

    测试数据:
        sql: 数据查询sql，查询结果只能有一个字段；
        取值序号: 查询出多条数据时取第几条，从1开始；
        查询检验失败重试超时时间(秒): 查询结果不合预期时，不断重试的时间；
        ssh_host: ssh主机IP地址；
        ssh_port: ssh主机端口；
        ssh_user: ssh用户名；
        ssh_passwd: ssh密码；
        db_host: 数据库主机IP；
        db_port: 数据库端口；
        db_user: 数据库用户名；
        db_passwd: 数据库密码；
        db_service_name: 数据库服务名；
    预期结果：
        text: 查询结果，按‘取值序号’取查询结果；
        description; 数据库返回的查询结果数量等描述；
    """
    data: dict = step['data']
    output: dict = step['output']
    expected: dict = step['expected']
    value_index = int(get_keyword_param(data, '取值序号', 1)) - 1
    expect_time_out = int(get_keyword_param(data, '失败重试超时时间', g.element_wait_timeout))
    allow_output_empty = get_keyword_param(data, '查询结果是否可以为空', '否')
    sql = data['sql']
    ssh_host = data['ssh_host']
    ssh_port = int(data['ssh_port'])
    ssh_user = data['ssh_user']
    ssh_passwd = data['ssh_passwd']
    db_host = data['db_host']
    db_port = data['db_port']
    db_user = data['db_user']
    db_passwd = data['db_passwd']
    db_service_name = data['db_service_name']
    rst = bwd_ssh_for_db(
        ssh_host=ssh_host, ssh_port=ssh_port, ssh_user=ssh_user, ssh_passwd=ssh_passwd, db_host=db_host,
        db_port=db_port, db_user=db_user, db_passwd=db_passwd, db_service_name=db_service_name, sql=sql
    )
    values = rst['values']
    desc = rst['description']

    if allow_output_empty == '否' and not values:
        time.sleep(2)
        t = time.time()
        while True:
            rst = bwd_ssh_for_db(
                ssh_host=ssh_host, ssh_port=ssh_port, ssh_user=ssh_user, ssh_passwd=ssh_passwd, db_host=db_host,
                db_port=db_port, db_user=db_user, db_passwd=db_passwd, db_service_name=db_service_name, sql=sql
            )
            values = rst['values']
            if values:
                desc = rst['description']
            elif time.time() - t > expect_time_out:
                raise Exception(f'数据库查询结果为空 {desc}')
            else:
                time.sleep(2)
    logger.info(f'查询结果：{rst}')

    values = [v.strip() for v in values]

    if expected:
        expected_success = False
        t = time.time()
        while not expected_success:
            for k, v in expected.items():
                expect = v
                if k == 'text':
                    real = values[value_index]
                elif k == 'description':
                    real = desc
                else:
                    raise Exception(f'当前关键字无此预期结果项: {k}')
                if real != real:
                    break
            else:
                expected_success = True
                logger.info(f'预期项:"{k}"，预期值:"{expect}"，当前值:"{real}"，预期结果校验成功！')
            if time.time() - t > expect_time_out and not expected_success:
                raise AssertionError(f'预期值"{expect}"，当前值"{real}"，预期结果校验失败！')

    for k, v in output.items():
        if v == 'text':
            g.update_var(k, value=values[value_index])


def execute_sql(step: dict):
    data: dict = step['data']
    output: dict = step['output']
    expected: dict = step['expected']

    sql = get_keyword_param(data, 'sql', gvar=True)
    # sql = sql.encode('gbk').decode('latin1')
    db_type: str = get_keyword_param(data, '数据库类型')
    db_host = get_keyword_param(data, '数据库主机IP', '', gvar=True)
    db_port = get_keyword_param(data, '数据库端口', '', gvar=True)
    db_user = get_keyword_param(data, '数据库用户名', gvar=True)
    db_passwd = get_keyword_param(data, '数据库密码', gvar=True)
    db_name = get_keyword_param(data, '数据库服务名或库名', '', gvar=True)
    # 查询oracle字符集 select userenv('language') from dual
    charset = data.get('数据库字符集', '')
    oracle_tns = get_keyword_param(data, 'oracle数据库tns', '', gvar=True)
    value_index = int(get_keyword_param(data, '取值序号', 1)) - 1
    value_index = value_index + 1 if value_index < 0 else value_index
    export_file = get_keyword_param(data, '导出到csv文件', '', gvar=True)
    retry_time = float(get_keyword_param(data, '重试时间', default=0))

    if db_type.lower() == 'mysql':
        assert db_host or db_port or db_name
        charset = charset if charset else 'utf8'
        db = records.Database(f'mysql://{db_user}:{db_passwd}@{db_host}:{db_port}/{db_name}?charset={charset}')
    elif db_type.lower() == 'oracle':
        assert db_host or db_port or db_name or oracle_tns, '请设置数据库参数或oracle数据库tns！'
        dsn_str = cx_Oracle.makedsn(db_host, db_port, service_name=db_name)
        dsn_str = oracle_tns if oracle_tns else dsn_str
        logger.info(f'连接数据库 {dsn_str}')
        os.environ["NLS_LANG"] = charset if charset else 'AMERICAN_AMERICA.ZHS16GBK'
        db = records.Database(f'oracle://{db_user}:{db_passwd}@{dsn_str}')
        db.get_connection()
    else:
        raise Exception('请填写数据库类型！')

    def query():
        try:
            conn = db.get_connection()
            res = conn.query(sql)
            rows = res.as_dict()
            # for row in rows:
            #     for k, v in row.items():
            #         row[k] = str(v) if isinstance(v, (int, float)) else v.encode('latin1').decode('gbk')
        # except OperationalError as err:
        #     logger.error(str(err))
        #     return
        # except ResourceClosedError:
        #     rows = []
        finally:
            try:
                conn.close()
            except:
                pass
        logger.info(f'sql执行结果："{rows}"')
        return res

    res = query()
    rows = res.as_dict()

    def check_expected(i):
        for k, v in expected.items():
            try:
                real = rows[i][k]
            except IndexError:
                raise IndexError(f'查询结果共【{len(rows)}】条，未获取到第【{value_index}】条数据！')
            except KeyError:
                raise KeyError(f'查询结果中未获取到名为【{k}】的字段，请重新配置输出数据！')
            logger.info(f'预期项:"{k}"，预期值为:"{v}"，当前值为:"{real}"')
            if v.lower() == 'null':
                assert not real, '与预期不符！'
            else:
                compare(v, str(real))

    # 预期结果校验
    end_time = time.time() + retry_time
    while True:
        try:
            if value_index == -1:
                logger.info(f'取值序号为 -1 将对所有查询结果进行校验')
                for i in range(len(rows)):
                    try:
                        check_expected(i)
                        break
                    except IndexError:
                        pass
                else:
                    raise Exception(f'对所有查询结果进行校验未发现预期项值')
                break
            else:
                check_expected(value_index)
                break
        except IndexError or AssertionError:
            if time.time() > end_time:
                raise
            time.sleep(1)
            res = query()
            rows = res.as_dict()

    if export_file and rows:
        f = pathlib.Path(result_path, export_file)
        f.parent.mkdir(parents=True, exist_ok=True)
        with open(f, 'w') as fp:
            fp.write(res.export('csv'))
        logger.info(f'数据成功导出到{export_file}')

    # 输出结果
    for var, data_key in output.items():
        try:
            value = rows[value_index][data_key]
        except IndexError:
            raise IndexError(f'查询结果共【{len(rows)}】条，未获取到第【{value_index}】条数据！')
        except KeyError:
            raise KeyError(f'查询结果中未获取到名为【{data_key}】的字段，请重新配置输出数据！')
        g.update_var(var, value=value)


if __name__ == '__main__':
    db = records.Database(f'mysql://root:DCtest_123.@localhost:3307/tdp?charset=utf8')
    try:
        res = db.query("select * from test_job where id>2160;")
        # s = 'a\nb\\nc'
        # s = 'c'
        # res = db.query("update test_result_set set log=JSON_ARRAY_APPEND(log, '$', %s) where id=1658;", s)
    except OperationalError as err:
        db.close()
        print(str(err))
        exit()
    try:
        rows = res.all()
    except ResourceClosedError:
        rows = None
    print(rows)

